const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.userModel');
const bcrypt = require('bcryptjs');
const CodeService = require('../services/codeService');
const { checkIfSuperAdmin } = require('../utils/utils');


class UserModel {    
    // 获取所有用户（过滤超级管理员）
    static async getAllUsers(page = 1, limit = 10, filters = {}, currentUserId = null) {
        const offset = (page - 1) * limit;
        let params = [];
        
        // 检查当前用户是否为超级管理员
        const isCurrentUserSuperAdmin = currentUserId ? await checkIfSuperAdmin(currentUserId, db, logger) : false;
        
        // 初始条件：排除删除状态
        let whereClause = 'WHERE u.is_delete = 0 AND u.is_protected = 0';
        
        // 如果不是超级管理员，则隐藏含有超级管理员角色的用户
        if (!isCurrentUserSuperAdmin) {
            whereClause += ' AND u.id NOT IN (SELECT DISTINCT ur.user_id FROM user_role ur INNER JOIN role r ON ur.role_id = r.id WHERE r.name = \'超级管理员\' AND r.is_delete = 0)';
        }

        // 构建过滤条件
        if (filters.username) {
            whereClause += ' AND u.username LIKE ?';
            params.push(`%${filters.username}%`);
        }
        if (filters.name) {
            whereClause += ' AND u.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.department_id) {
            whereClause += ' AND u.department_id = ?';
            params.push(filters.department_id);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND u.status = ?';
            params.push(filters.status);
        }

        // 搜索过滤
        if (filters.searchvalue !== undefined) {
            whereClause += ' AND (u.code LIKE ? OR u.name LIKE ? OR u.phone LIKE ? OR u.username LIKE ?)';
            const searchTerm = `%${filters.searchvalue}%`;
            params.push(searchTerm, searchTerm, searchTerm, searchTerm);
        }

        const sql = `
            SELECT 
                u.id,
                u.username,
                u.code,
                u.name,
                u.address_ids,
                u.detail_address,
                u.phone,
                u.email,
                u.address,
                u.department_id,
                u.avatar_url,
                u.status,
                u.is_protected,
                u.gender,
                u.created_at,
                u.updated_at,
                u.create_by,
                u.update_by,
                (SELECT name FROM user WHERE id = u.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = u.update_by AND is_delete = 0) as update_by_name,
                d.name as department_name,
                d.code as department_code,
                d.type as department_type,
                (SELECT COUNT(*) FROM user_role ur INNER JOIN role r ON ur.role_id = r.id WHERE ur.user_id = u.id AND r.name = '超级管理员' AND r.is_delete = 0) > 0 as has_super_admin_role,
                (SELECT GROUP_CONCAT(ur.role_id) FROM user_role ur INNER JOIN role r ON ur.role_id = r.id WHERE ur.user_id = u.id AND r.is_delete = 0) as role_ids
            FROM user u
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            ${whereClause}
            ORDER BY u.created_at DESC
            LIMIT ${Number(limit)} OFFSET ${Number(offset)}
        `;


        try {
            const [rows] = await db.execute(sql,params);
            // 将roleIds字符串转换为数组
            return rows.map(row => ({
                ...row,
                role_ids: row.role_ids ? row.role_ids.split(',').map(id => parseInt(id, 10)) : []
            }));
        } catch (error) {
            logger.error('获取所有用户失败', { error });
            throw error;
        }
    }

    // 获取用户总数（根据权限过滤）
    static async getUserCount(filters = {}, currentUserId = null) {
        let params = [];
        
        // 检查当前用户是否为超级管理员
        const isCurrentUserSuperAdmin = currentUserId ? await checkIfSuperAdmin(currentUserId, db, logger) : false;
        
        // 初始条件：排除删除状态
        let whereClause = 'WHERE u.is_delete = 0 AND is_protected = 0';
        
        // 如果不是超级管理员，则隐藏含有超级管理员角色的用户
        if (!isCurrentUserSuperAdmin) {
            whereClause += ' AND u.id NOT IN (SELECT DISTINCT ur.user_id FROM user_role ur INNER JOIN role r ON ur.role_id = r.id WHERE r.name = \'超级管理员\' AND r.is_delete = 0)';
        }

        // 构建过滤条件
        if (filters.username) {
            whereClause += ' AND u.username LIKE ?';
            params.push(`%${filters.username}%`);
        }
        if (filters.name) {
            whereClause += ' AND u.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.department_id) {
            whereClause += ' AND u.department_id = ?';
            params.push(filters.department_id);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND u.status = ?';
            params.push(filters.status);
        }

        const sql = `SELECT COUNT(*) as total FROM user u ${whereClause}`;

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].total;
        } catch (error) {
            logger.error('获取用户总数失败', { error });
            throw error;
        }
    }

    // 创建用户
    static async create(userData, currentUserId = null) {
        
        let { username, password, name, phone, email, address, code, department_id, avatar_url,address_ids,detail_address, gender } = userData;
        
        // 如果未提供用户编码，自动生成
        if (!code) {
            try {
                code = await CodeService.generateUniqueCode('USER_CODE');
                logger.info('自动生成用户编码', { code, username });
            } catch (error) {
                logger.error('生成用户编码失败', { error, username });
                throw new Error('生成用户编码失败');
            }
        }


        // 密码加密
        const salt = bcrypt.genSaltSync(10);
        const hashedPassword = bcrypt.hashSync(password, salt);

        const sql = `
            INSERT INTO user (username, password, name, phone, email, address, code, department_id, avatar_url,address_ids,detail_address, gender, create_by, update_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;
        
        try {

            const params = [username, hashedPassword, name, phone || '', email || '', address || '', code, department_id, avatar_url,address_ids||'',detail_address||'', gender, currentUserId, currentUserId];

            const [result] = await db.execute(sql, params);
            return result.insertId;
        } catch (error) {
            logger.error('创建用户失败', { error });
            throw error;
        }
    }

    // 更新用户
    static async update(id, updateData, currentUserId = null) {
        // 检查要更新的用户是否含有超级管理员角色
        const hasSuperAdminRole = await this.hasSuperAdminRole(id,'SUPER_ADMIN');
        
        // 如果要更新的用户是超级管理员，则拒绝更新（即使当前用户是超级管理员也不允许）
        if (hasSuperAdminRole) {
            logger.warn('尝试更新超级管理员用户', { userId: id, currentUserId });
            return 0;
        }
        
        const { username, password, name, phone, email, address, code, department_id, avatar_url, status ,address_ids,detail_address, gender,update_by=currentUserId} = updateData;
        
        // 动态构建更新SQL
        let sql = 'UPDATE user SET updated_at = NOW(), update_by = ?,';
        // 初始化参数数组并添加currentUserId作为update_by的值
        const params = [currentUserId];
        
        if (username !== undefined) { sql += ' username = ?, '; params.push(username); }
        if (password !== undefined) {
            // 密码加密
            const salt = bcrypt.genSaltSync(10);
            const hashedPassword = bcrypt.hashSync(password, salt);
            sql += ' password = ?, ';
            params.push(hashedPassword);
        }
        if (name !== undefined) { sql += ' name = ?, '; params.push(name); }
        if (phone !== undefined) { sql += ' phone = ?, '; params.push(phone); }
        if (email !== undefined) { sql += ' email = ?, '; params.push(email); }
        if (address !== undefined) { sql += ' address = ?, '; params.push(address); }
        if (code !== undefined) { sql += ' code = ?, '; params.push(code); }
        if (department_id !== undefined) { sql += ' department_id = ?, '; params.push(department_id); }
        if (avatar_url !== undefined) { sql += ' avatar_url = ?, '; params.push(avatar_url); }
        if (status !== undefined) { sql += ' status = ?, '; params.push(status); }
        if (address_ids !== undefined) { sql += ' address_ids = ?, '; params.push(address_ids); }
        if (detail_address !== undefined) { sql += ' detail_address = ?, '; params.push(detail_address); }
        if (gender !== undefined) { sql += ' gender = ?, '; params.push(gender); }
        
        // 移除最后一个逗号和空格
        sql = sql.replace(/,\s*$/, '');
        
        // 添加WHERE子句
        sql += ' WHERE id = ? AND is_delete = 0';
        params.push(id);

        try {
            const [result] = await db.execute(sql, params);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新用户失败', { error });
            throw error;
        }
    }

    // 删除用户（软删除）
    static async deleteById(id, currentUserId = null) {
        // 检查要删除的用户是否含有超级管理员角色
        const hasSuperAdminRole = await this.hasSuperAdminRole(id,'SUPER_ADMIN');
        
        // 如果要删除的用户是超级管理员，则拒绝删除
        if (hasSuperAdminRole) {
            logger.warn('尝试删除超级管理员用户', { userId: id, currentUserId });
            return 0;
        }
        
        const sql = `
            UPDATE user 
            SET is_delete = 1, updated_at = NOW()
            WHERE id = ? AND is_delete = 0 AND is_protected = 0
        `;

        try {
            const [result] = await db.execute(sql, [id]);
            return result.affectedRows;
        } catch (error) {
            logger.error('删除用户失败', { error, userId: id });
            throw error;
        }
    }

    // 批量删除用户（软删除）
    static async deleteBatch(ids, connection = null, currentUserId = null) {
        if (!ids || !Array.isArray(ids) || ids.length === 0) {
            throw new Error('无效的用户ID列表');
        }
        
        // 检查是否包含超级管理员用户
        const superAdminUsers = [];
        for (const id of ids) {
            if (await this.hasSuperAdminRole(id,'SUPER_ADMIN')) {
                superAdminUsers.push(id);
            }
        }
        
        // 如果包含超级管理员用户，则记录警告并过滤这些用户
        if (superAdminUsers.length > 0) {
            logger.warn('尝试批量删除包含超级管理员的用户列表', { superAdminUserIds: superAdminUsers, currentUserId });
            // 过滤掉超级管理员用户ID
            ids = ids.filter(id => !superAdminUsers.includes(id));
            
            // 如果过滤后没有要删除的用户，直接返回0
            if (ids.length === 0) {
                return 0;
            }
        }

        // 使用预处理语句防止SQL注入
        const placeholders = ids.map(() => '?').join(',');
        const sql = `
            UPDATE user 
            SET is_delete = 1, updated_at = NOW()
            WHERE id IN (${placeholders}) AND is_delete = 0 AND is_protected = 0
        `;

        try {
            const execDb = connection || db;
            const [result] = await execDb.execute(sql, ids);
            return result.affectedRows;
        } catch (error) {
            logger.error('批量删除用户失败', { error, userIds: ids });
            throw error;
        }
    }




    // 更新用户头像路径
    static async updateUserAvatar(userId, avatarPath) {
        const sql = `
            UPDATE user 
            SET avatar_url = ?, updated_at = NOW() 
            WHERE id = ? AND is_delete = 0
        `;
        
        try {
            const [result] = await db.execute(sql, [avatarPath, userId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新用户头像失败', { error, userId });
            throw error;
        }
    }
    
    // 重置用户密码
    static async resetPassword(id, currentUserId) {
        // 检查要重置密码的用户是否含有超级管理员或系统管理员角色
        const hasSuperAdminRole = await this.hasSuperAdminRole(id, 'ALL');
        
        // 如果要重置密码的用户是超级管理员，则拒绝操作
        if (hasSuperAdminRole) {
            logger.warn('尝试重置超级管理员用户密码', { userId: id, currentUserId });
            return 0;
        }
        
        // 初始密码固定为123456
        const initialPassword = '123456';
        
        // 密码加密
        const salt = bcrypt.genSaltSync(10);
        const hashedPassword = bcrypt.hashSync(initialPassword, salt);
        
        const sql = `
            UPDATE user 
            SET password = ?, updated_at = NOW(), update_by = ?
            WHERE id = ? AND is_delete = 0 AND is_protected = 0
        `;
        
        try {
            const [result] = await db.execute(sql, [hashedPassword, currentUserId, id]);
            
            if (result.affectedRows > 0) {
                // 返回新密码信息，以便前端显示
                return { affectedRows: result.affectedRows, newPassword: initialPassword };
            }
            
            return { affectedRows: 0, newPassword: null };
        } catch (error) {
            logger.error('重置用户密码失败', { error, userId: id });
            throw error;
        }
    }
    
    // 检查用户是否含有超级管理员角色或系统管理员角色
    // roleType参数可选值：'SUPER_ADMIN'(仅检查超级管理员)、'ADMIN'(仅检查系统管理员)、'ALL'(检查任一管理员角色)
    static async hasSuperAdminRole(userId, roleType = 'ALL') {
        let whereCondition = '';
        let params = [userId];
        
        // 根据角色类型构建查询条件
        if (roleType === 'SUPER_ADMIN') {
            // 仅检查超级管理员角色
            whereCondition = 'r.code = ?';
            params.push('SUPER_ADMIN');
        } else if (roleType === 'ADMIN') {
            // 仅检查系统管理员角色
            whereCondition = 'r.code = ?';
            params.push('ADMIN');
        } else {
            // 检查任一管理员角色（默认行为）
            whereCondition = 'r.code IN (?, ?)';
            params.push('SUPER_ADMIN', 'ADMIN');
        }
        
        const sql = `
            SELECT COUNT(*) as count 
            FROM user_role ur
            INNER JOIN role r ON ur.role_id = r.id
            WHERE ur.user_id = ? AND ${whereCondition} AND r.is_delete = 0
        `;
        
        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查用户是否含有管理员角色失败', { error, userId, roleType });
            return false;
        }
    }

    // 根据ID获取用户
    static async findById(id, currentUserId = null) {
        // 检查要查询的用户是否含有超级管理员角色
        const hasSuperAdminRole = await this.hasSuperAdminRole(id,'SUPER_ADMIN');
        
        // 检查当前用户是否为超级管理员
        const isCurrentUserSuperAdmin = currentUserId ? await this.hasSuperAdminRole(currentUserId,'SUPER_ADMIN') : false;
        
        // 如果要查询的用户是超级管理员，且当前用户不是超级管理员，则拒绝访问
        if (hasSuperAdminRole && !isCurrentUserSuperAdmin) {
            return null;
        }
        
        const sql = `
            SELECT 
                u.id,
                u.username,
                u.code,
                u.name,
                u.phone,
                u.email,
                u.address,

                u.department_id,
                u.avatar_url,
                u.status,
                u.is_protected,
                u.gender,
                u.created_at,
                u.updated_at,
                u.create_by,
                u.update_by,
                (SELECT name FROM user WHERE id = u.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = u.update_by AND is_delete = 0) as update_by_name,
                d.name as department_name,
                d.code as department_code,
                d.type as department_type
            FROM user u
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            WHERE u.id = ? AND u.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            const user = rows[0] || null;
            
            if (user) {
                // 查询用户绑定的所有角色信息
                const rolesSql = `
                    SELECT 
                        r.id,
                        r.name,
                        r.code,
                        r.description,
                        ur.created_at as bind_time
                    FROM role r
                    INNER JOIN user_role ur ON r.id = ur.role_id
                    WHERE ur.user_id = ? AND r.is_delete = 0
                    ORDER BY ur.created_at DESC
                `;
                
                const [rolesRows] = await db.execute(rolesSql, [user.id]);
                user.roles = rolesRows;
                
                // 直接判断用户是否为管理员，添加is_admin字段
                // 只有管理员用户可以看到其他用户的is_admin状态
                // if (currentUserHasAdminRole) {
                //     // 检查用户是否拥有超级管理员或管理员角色
                //     const adminRoleCodes = ['SUPER_ADMIN', 'ADMIN'];
                //     user.is_admin = rolesRows.some(role => adminRoleCodes.includes(role.code));
                // } else {
                //     // 非管理员只能看到自己的管理员状态
                //     if (currentUserId && currentUserId === user.id) {
                //         const adminRoleCodes = ['SUPER_ADMIN', 'ADMIN'];
                //         user.is_admin = rolesRows.some(role => adminRoleCodes.includes(role.code));
                //     }
                // }
            }
            
            return user;
        } catch (error) {
            logger.error('根据ID获取用户失败', { error });
            throw error;
        }
    }

    // 根据用户名查找用户
    static async findByUsername(username, currentUserId = null) {
        // 构建查询字段，只有管理员才返回is_admin字段
        let selectFields = `
                u.id,
                u.username,
                u.code,
                u.name,
                u.phone,
                u.email,
                u.address,
                u.detail_address,
                u.department_id,
                u.avatar_url,
                u.status,
                u.is_protected,
                u.password,
                u.gender,
                u.created_at,
                u.updated_at,
                u.create_by,
                u.update_by,
                (SELECT name FROM user WHERE id = u.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = u.update_by AND is_delete = 0) as update_by_name,
                d.name as department_name,
                d.code as department_code,
                d.type as department_type`;
    
        
        const userSql = `
            SELECT ${selectFields}
            FROM user u
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            WHERE u.username = ? AND u.is_delete = 0
        `;

        try {
            const [userRows] = await db.execute(userSql, [username]);
            const user = userRows[0] || null;
            
            if (user) {
                // 查询用户绑定的所有角色信息
                const rolesSql = `
                    SELECT 
                        r.id,
                        r.name,
                        r.code,
                        r.description,
                        ur.created_at as bind_time
                    FROM role r
                    INNER JOIN user_role ur ON r.id = ur.role_id
                    WHERE ur.user_id = ? AND r.is_delete = 0
                    ORDER BY ur.created_at DESC
                `;
                
                const [rolesRows] = await db.execute(rolesSql, [user.id]);
                // user.roles = rolesRows;
                
                // 直接判断用户是否为管理员，添加is_admin字段
                // 只有管理员用户可以看到其他用户的is_admin状态
                if (rolesRows.length > 0) {
                    // 检查用户是否拥有超级管理员或管理员角色
                    const adminRoleCodes = ['SUPER_ADMIN', 'ADMIN'];
                    user.is_admin = rolesRows.some(role => adminRoleCodes.includes(role.code));
                }
            }
            
            return user;
        } catch (error) {
            logger.error('根据用户名查找用户失败', { error });
            throw error;
        }
    }

    // 根据手机号查找用户
    static async findByMobile(phone) {
        const sql = `
            SELECT 
                u.id,
                u.username,
                u.code,
                u.name,
                u.phone,
                u.email,
                u.address,
                u.department_id,
                u.avatar_url,
                u.status,
                u.is_protected,
                u.gender,
                u.created_at,
                u.updated_at,
                u.create_by,
                u.update_by,
                (SELECT name FROM user WHERE id = u.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = u.update_by AND is_delete = 0) as update_by_name,
                d.name as department_name,
                d.code as department_code,
                d.type as department_type
            FROM user u
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            WHERE u.phone = ? AND u.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [phone]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据手机号查找用户失败', { error });
            throw error;
        }
    }

    // 检查用户名是否存在
    static async isUsernameExists(username, excludeId = null) {
        let sql = 'SELECT COUNT(*) as count FROM user WHERE username = ? AND is_delete = 0';
        let params = [username];

        if (excludeId) {
            sql += ' AND id != ?';
            params.push(excludeId);
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查用户名是否存在失败', { error });
            throw error;
        }
    }

    // 检查手机号是否存在
    static async isMobileExists(phone, excludeId = null) {
        let sql = 'SELECT COUNT(*) as count FROM user WHERE phone = ? AND is_delete = 0';
        let params = [phone];

        if (excludeId) {
            sql += ' AND id != ?';
            params.push(excludeId);
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查手机号是否存在失败', { error });
            throw error;
        }
    }

    

    // 检查用户是否可以删除
    static async isDeletable(id) {
        // 首先检查用户是否含有超级管理员角色
        const hasSuperAdminRole = await this.hasSuperAdminRole(id, 'SUPER_ADMIN');
        if (hasSuperAdminRole) {
            return false; // 超级管理员用户不可删除
        }
        
        const sql = `
            SELECT is_protected
            FROM user u
            WHERE u.id = ? AND u.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            if (rows.length === 0) return false;
            
            const user = rows[0];
            return user.is_protected === 0; // 只有非保护用户才能删除
        } catch (error) {
            logger.error('检查用户是否可删除失败', { error });
            throw error;
        }
    }

    // 获取用户角色
    static async getUserRoles(userId) {
        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.description,
                ur.created_at as bind_time
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            WHERE ur.user_id = ? AND r.is_delete = 0
            ORDER BY ur.created_at DESC
        `;

        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows;
        } catch (error) {
            logger.error('获取用户角色失败', { error });
            throw error;
        }
    }


    // 获取用户已绑定的角色
    static async getBindRoles(userId,page,limit) {
        const offset = (page - 1) * limit;

        const sql = `
            SELECT 
                r.id,       
                r.name,
                r.code,
                r.description,
                ur.created_at as bind_time
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            WHERE ur.user_id = ? AND r.is_delete = 0
            ORDER BY ur.created_at DESC
            LIMIT ${Number(limit)} OFFSET ${Number(offset)}
        `;
        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows;
        } catch (error) {
            logger.error('获取用户已绑定角色失败', { error });
            throw error;
        }
    }

    // 获取用户已绑定角色数量
    static async getBindRoleCount(userId,filters={}) {
        const sql = `
            SELECT COUNT(*) as count
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            WHERE ur.user_id = ? AND r.is_delete = 0
        `;
        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows[0].count;
        } catch (error) {
            logger.error('获取用户已绑定角色数量失败', { error });
            throw error;
        }
    }


    // 用户绑定角色
    static async bindRoles(userId, roleIds , currentUserId = null, bindtype='update') {
        if (!Array.isArray(roleIds) || roleIds.length === 0) {
            return 0;
        }
        
        if(bindtype==='update'){
            // 先删除现有绑定
            await this.unbindAllRoles(userId);
        }
        

        // 插入新的绑定
        const values = roleIds.map(roleId => [userId, roleId, currentUserId, currentUserId]);
        const placeholders = values.map(() => '(?, ?, ?, ?)').join(',');
        const sql = `INSERT INTO user_role (user_id, role_id, create_by, update_by) VALUES ${placeholders}`;
        const flattenedValues = values.flat();

        try {
            const [result] = await db.execute(sql, flattenedValues);
            return result.affectedRows;
        } catch (error) {
            logger.error('用户绑定角色失败', { error });
            throw error;
        }
    }

    // 解除用户所有角色绑定
    static async unbindAllRoles(userId) {
        const sql = 'DELETE FROM user_role WHERE user_id = ?';

        try {
            const [result] = await db.execute(sql, [userId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('解除用户所有角色绑定失败', { error });
            throw error;
        }
    }

    // 获取用户角色绑定信息
    static async getBindInfo(userRoleId) {
        const sql = 'SELECT id, user_id, role_id FROM user_role WHERE id = ?';
        
        try {
            const [rows] = await db.execute(sql, [userRoleId]);
            return rows[0] || null;
        } catch (error) {
            logger.error('获取用户角色绑定信息失败', { error });
            throw error;
        }
    }
    
    // 解除用户角色绑定
    static async unbindRole(userRoleId) {
        const deleteSql = 'DELETE FROM user_role WHERE id = ?';
        try {
            const [result] = await db.execute(deleteSql, [userRoleId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('解除用户角色绑定失败', { error });
            throw error;
        }
    }

    // 检查用户是否为部门负责人
    static async isDepartmentManager(userId) {
        const sql = 'SELECT COUNT(*) AS count FROM department WHERE manager_id = ? AND is_delete = 0';
        try {
            const [result] = await db.execute(sql, [userId]);
            return result[0].count > 0;
        } catch (error) {
            logger.error('检查用户是否为部门负责人失败', { error, userId });
            throw error;
        }
    }
    
    // 根据用户ID获取菜单
    // 支持access_type权限过滤：0-公共菜单(默认访问)，1-私有菜单(需要角色绑定)
    static async findMenusByUserId(userId) {
        
        // 检查是否为超级管理员
        const adminRoleSql = `
            SELECT r.id FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            WHERE ur.user_id = ? AND r.name = '超级管理员' AND r.is_delete = 0 AND r.is_protected = 1
        `;

        try {
            const [adminRole] = await db.execute(adminRoleSql, [userId]);
            if (adminRole.length > 0) {
                // 超级管理员返回所有菜单
                const [menus] = await db.execute('SELECT * FROM menu WHERE is_delete = 0 ORDER BY parent_id ASC, id ASC');
                return menus;
            }

            // 普通用户根据access_type和角色权限过滤菜单
            // 1. access_type为0的菜单默认允许访问
            // 2. access_type为1的菜单需要角色绑定
            const menusSql = `
                SELECT DISTINCT m.* 
                FROM menu m
                LEFT JOIN role_menu rm ON m.id = rm.menu_id
                LEFT JOIN user_role ur ON rm.role_id = ur.role_id AND ur.user_id = ?
                WHERE m.is_delete = 0
                AND (m.access_type = 0 OR (m.access_type = 1 AND ur.user_id = ?))
                ORDER BY m.parent_id ASC, m.id ASC
            `;
            const [menus] = await db.execute(menusSql, [userId, userId]);
            
            return menus;
        } catch (error) {
            logger.error('根据用户ID获取菜单失败', { error });
            throw error;
        }
    }

    // 获取部门用户统计
    static async getDepartmentUserStats() {
        const sql = `
            SELECT 
                d.id as department_id,
                d.name as department_name,
                d.code as department_code,
                COUNT(u.id) as user_count,
                COUNT(CASE WHEN u.status = 0 THEN 1 END) as active_user_count,
                COUNT(CASE WHEN u.status = 1 THEN 1 END) as inactive_user_count
            FROM department d
            LEFT JOIN user u ON d.id = u.department_id AND u.is_delete = 0
            WHERE d.is_delete = 0
            GROUP BY d.id, d.name, d.code
            ORDER BY d.parent_id ASC, d.id ASC
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows;
        } catch (error) {
            logger.error('获取部门用户统计失败', { error });
            throw error;
        }
    }

    // 批量更新用户部门
    static async updateUsersDepartment(userIds, departmentId) {
        if (!Array.isArray(userIds) || userIds.length === 0) {
            return 0;
        }

        const sql = `
            UPDATE user 
            SET department_id = ?, updated_at = NOW()
            WHERE id IN (${userIds.map(() => '?').join(',')}) AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [departmentId, ...userIds]);
            return result.affectedRows;
        } catch (error) {
            logger.error('批量更新用户部门失败', { error });
            throw error;
        }
    }

    // 获取用户统计信息
    static async getUserStats() {
        const sql = `
            SELECT 
                COUNT(*) as total_users,
                COUNT(CASE WHEN status = 0 THEN 1 END) as active_users,
                COUNT(CASE WHEN status = 1 THEN 1 END) as inactive_users,
                COUNT(CASE WHEN is_protected = 1 THEN 1 END) as protected_users,
                COUNT(CASE WHEN department_id IS NOT NULL THEN 1 END) as users_with_department,
                COUNT(CASE WHEN department_id IS NULL THEN 1 END) as users_without_department
            FROM user 
            WHERE is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows[0];
        } catch (error) {
            logger.error('获取用户统计信息失败', { error });
            throw error;
        }
    }

    // 批量删除用户及其关联角色（事务处理）
    static async deleteBatchWithRoles(idsArray, currentUserId = null) {
        if (!Array.isArray(idsArray) || idsArray.length === 0) {
            return 0;
        }

        const connection = await db.getConnection();
        try {
            logger.info('开始事务执行批量删除用户及其关联角色', { ids: idsArray });
            await connection.beginTransaction();
            
            // 删除用户
            const deleteResult = await this.deleteBatch(idsArray, connection, currentUserId);
            
            // 批量删除用户角色关联数据，避免循环调用
            const placeholders = idsArray.map(() => '?').join(',');
            const deleteRoleSql = `DELETE FROM user_role WHERE user_id IN (${placeholders})`;
            await connection.execute(deleteRoleSql, idsArray);
            
            await connection.commit();
            logger.info('批量删除用户及其关联角色成功', { ids: idsArray });
            return deleteResult;
        } catch (error) {
            await connection.rollback();
            logger.error('批量删除用户及其关联角色失败', { error, ids: idsArray });
            throw error;
        } finally {
            connection.release();
        }
    }
}

module.exports = UserModel;